package com.xishu.dao.jpa;

import com.xishu.entity.plat.ReportData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface ReportDataDao extends JpaRepository<ReportData, Long>, JpaSpecificationExecutor<ReportData> {

    /**
     * C端的连接最大人数统计
     * <p>
     * 按照开始时间，结束时间，商店ID做索引
     * 然后按照数据类型做过滤, CACHE_REPORT_TYPE_ONLINE_NUMBER_IN_C = 1;
     * 再分组求最大值
     *
     * @return
     */
    @Query("select  FLOOR(create_time/:timeUnit) as xTime , cast(shop_id as char) as y, max(value) as value from report_data m where  m.createTime >= :startTime and m.createTime <= :endTime  and m.shopId =:shopId  and type = 1 group by FLOOR(m.createTime/:timeUnit)")
    public List<Tuple> onlineInC(Long shopId, Long startTime, Long endTime, Long timeUnit);

    /**
     * 订单数量
     * 订单情况--实时上座情况；折线图，超过5分钟一个点的情况，求范围内的累加值
     * 按照开始时间，结束时间，商店ID做索引
     * 然后按照数据类型做过滤, CACHE_REPORT_TYPE_ORDER_NUMBER = 2;
     * 再分组，再求累加值
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query("select  FLOOR(create_time/:timeUnit) as xTime , cast(shop_id as char) as y, sum(value)  as value from report_data m where  m.createTime >= :startTime and m.createTime <= :endTime  and m.shopId =:shopId  and type = 2  group by FLOOR(m.createTime/:timeUnit)")
    public List<Tuple> reportOrderNumber(Long shopId, Long startTime, Long endTime, Long timeUnit);

    /**
     * 已支付订单数量
     * 订单情况--实时上座情况；折线图，超过5分钟一个点的情况，求范围内的累加值
     * 按照开始时间，结束时间，商店ID做索引
     * 然后按照数据类型做过滤, CACHE_REPORT_TYPE_ORDER_PAYED = 3;
     * 再分组，再求累加值
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query("select  FLOOR(create_time/:timeUnit) as xTime , cast(shop_id as char) as y, sum(value) as value from report_data m where  m.createTime >= :startTime and m.createTime <= :endTime  and m.shopId =:shopId  and type = 3 group by FLOOR(m.createTime/:timeUnit)")
    public List<Tuple> reportPayedOrderNumber(Long shopId, Long startTime, Long endTime, Long timeUnit);
}
